package com.jiuyun.dao.imol;

import com.jiuyun.dao.ClassesDao;
import com.jiuyun.entiy.Classes;
import com.jiuyun.entiy.Homework;
import com.jiuyun.entiy.Student;
import com.jiuyun.utils.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;


public class ClassesDaoImpl implements ClassesDao {
    QueryRunner runner= new QueryRunner();

    @Override
    public List getClasses() throws SQLException {
        String sql="select * from classes";
        Connection conn= AliPool.getConn();
        List list=runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
        conn.close();
        return list;
    }

    @Override
    public List getStudents(int classId) throws SQLException {
        String sql="select * from user where classId=?";
        Connection conn= AliPool.getConn();
        List list=runner.query(conn,sql,new BeanListHandler<Student>(Student.class),classId);
        conn.close();
        return list;
    }

    @Override
    public List getHomework(int classId, int year, int month) throws SQLException {
        String sql="select h.*,s.userName from user s " +
                "inner join classes c on c.classId=s.classId "+
                "inner join homework h on h.studentId=s.studentId "+
                "where c.classId=? and YEAR (ctime)=? and MONTH(ctime)=?";
        Connection conn= AliPool.getConn();
        List list=runner.query(conn,sql,new BeanListHandler<Homework>(Homework.class),classId,year,month);
        conn.close();
        return list;
    }
}
